﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Assignment
{
    public partial class Assignment3 : Form
    {
        private DataSet customerDataSet;
        public Assignment3()
        {
            InitializeComponent();
        }

        private void Assignment3_Load(object sender, EventArgs e)
        {
            customerDataSet = GetCustomers();
            List<ListViewItem> listViewItems = ConvertToListViewItems();
            
            customerListView.Items.Clear();
            customerListView.Items.AddRange(listViewItems.ToArray());
        }

        private List<ListViewItem> ConvertToListViewItems()
        {
            List<ListViewItem> listViewItems = new List<ListViewItem>();
            foreach (DataRow row in customerDataSet.Tables["Customers"].Rows)
            {
                ListViewItem item = new ListViewItem();

                item.Text = (string) row["CompanyName"];
                item.SubItems.Add((string) row["ContactName"]);
                item.SubItems.Add((string) row["Phone"]);
                item.ImageIndex = 0;

                listViewItems.Add(item);
            }
            return listViewItems;
        }

        private static DataSet GetCustomers()
        {
            DataSet customerDataSet;
            
            using (SqlConnection sqlConnection = new SqlConnection())
            {
                sqlConnection.ConnectionString =
                    "Data Source = DELL-N5110; Initial Catalog = Northwind; Integrated Security = True;";
                SqlCommand selectCommand = new SqlCommand("SELECT * FROM Customers", sqlConnection);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand);

                customerDataSet = new DataSet();

                sqlConnection.Open();
                sqlDataAdapter.Fill(customerDataSet, "Customers");
                sqlConnection.Close();
            }
            
            return customerDataSet;
        }

        private void addButton_Click(object sender, EventArgs e)
        {
            DataTable customerTable = customerDataSet.Tables["Customers"];
            DataRow newRow = customerTable.NewRow();

            newRow["CustomerID"] = customerIDText.Text;
            newRow["CompanyName"] = companyText.Text;
            newRow["ContactName"] = contactText.Text;
            newRow["Phone"] = phoneText.Text;

            customerTable.Rows.Add(newRow);

            UpdateDataSet();
            Assignment3_Load(null, null);
        }

        private void UpdateDataSet()
        {
            using (SqlConnection sqlConnection = new SqlConnection())
            {
                sqlConnection.ConnectionString =
                    "Data Source = DELL-N5110; Initial Catalog = Northwind; Integrated Security = True;";
                SqlCommand insertCommand = new SqlCommand("INSERT INTO Customers(CustomerID, CompanyName, ContactName, Phone) VALUES (@CustomerID, @CompanyName, @ContactName, @Phone)",
                                                          sqlConnection);

                insertCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
                insertCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
                insertCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30, "ContactName");
                insertCommand.Parameters.Add("@Phone", SqlDbType.NVarChar, 24, "Phone");

                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.InsertCommand = insertCommand;

                sqlConnection.Open();
                sqlDataAdapter.Update(customerDataSet, "Customers");
                sqlConnection.Close();
            }
        }
    }
}